package com.walker.service.panorama;

import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.time.DateFormatUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import com.walker.bean.MetricPO;
import com.walker.util.CommonUtils;
import com.walker.util.DateUtils;

@Service("assetPanoramaService")
public class AssetPanoramaService {

	@Autowired
	@Qualifier("dmJdbcTemplate")
	private JdbcTemplate dmJdbcTemplate;
	
	@Value("${sentinelDB:sentinel_db}")
	private String sentinelDB;
	
	/**
	 * 项目视图-球数据明细
	 * @return
	 */
	public List<Map<String,Object>> getBusinessPlateViewInfo(){
		StringBuffer sql = new StringBuffer();
		sql.append("select a.busin_name as project_name,");
		sql.append("       a.resource_use,");
		sql.append("       a.storage_use, ");
		sql.append("       a.busin_name,");
		sql.append("       a.cube_num,");
		sql.append("       a.dim_logic_num, ");
		sql.append("       a.fact_logic_num,");
		sql.append("       a.sum_num, ");
		sql.append("       a.storage_unit,");
		sql.append("       a.tidb_num,");
		sql.append("       a.es_num,");
		sql.append("       a.hive_num,");
		sql.append("       c.param_value as queue_name, ");
		sql.append("       concat(d.capacity_memory, 'MB') as capacity_memory,");
		sql.append("       concat(d.used_memory, 'MB') as used_memory ");
		sql.append("  from t_asset_v_global a ");
		sql.append(" inner join t_meta_project b");
		sql.append("    on a.project_id = b.id");
		sql.append("  left join (select * ");
		sql.append("               from t_meta_engine_param ");
		sql.append("              where param_key = 'spark_yarn_queue') c ");
		sql.append("    on b.engine_id = c.id ");
		sql.append("  left join (select queue_name, ");
		sql.append("                    max(mineffective_capacity_memory) capacity_memory,");
		sql.append("                    max(resources_used_memory) used_memory");
		sql.append("               from yarn_metrics.t_f_yarn_scheduler_metrics_details tfycm ");
		sql.append("              where DATE_FORMAT(created_time, '%Y-%m-%d') = DATE_FORMAT(NOW(), '%Y-%m-%d')");
		sql.append("              group by queue_name) d");
		sql.append("    on c.param_value = d.queue_name ");
		return dmJdbcTemplate.queryForList(sql.toString());
	}
	
	/**
	 * 全景总览信息
	 * 原依业务板块统计，改为按项目统计
	 * @return
	 */
	public Map<String,Object> getPanoramicOverviewInfo(){
		StringBuffer sql = new StringBuffer();
		sql.append("select g.project_name,");
		sql.append("       storage_use,");
		sql.append("       storage_unit,");
		sql.append("       ROUND(g.storage_use / s.storage_sum, 2) as percentage");
		sql.append("  from t_asset_v_global g,");
		sql.append("       (select sum(storage_use) as storage_sum");
		sql.append("          from t_asset_v_global) s ORDER BY STORAGE_USE desc");
		List<Map<String,Object>> list = dmJdbcTemplate.queryForList(sql.toString());
		
		String sumSql = "select sum(storage_use) as storage_sum,sum(task_num) as task_sum,sum(cube_num) as cube_sum,count(*) as project_sum,storage_unit from t_asset_v_global";
		Map<String,Object> datas = dmJdbcTemplate.queryForMap(sumSql);
		datas.put("list", list);
		
		List<Map<String, Object>> used=dmJdbcTemplate.queryForList("select max(memory_total) memory_total,max(memory_used)memory_used from yarn_metrics.t_f_yarn_cluster_metrics where DATE_FORMAT(created_time,'%Y-%m-%d') =DATE_FORMAT(NOW(),'%Y-%m-%d')");
		if(used!=null && used.size()!=0){
			datas.put("memory_total", used.get(0).get("memory_total")+"MB");
			datas.put("memory_used", used.get(0).get("memory_used")+"MB");
		}else{
			datas.put("memory_total", "0MB");
			datas.put("memory_used", "0MB");
		}
		return datas;
	}
	
	/**
	 * 流动总表数
	 * @return
	 */
	public List<Map<String,Object>> getFlowTablesNumber(){
		StringBuffer sql = new StringBuffer();
		sql.append("select sum(cube_num) as cube_num,");
		sql.append("       sum(dim_sum) as dim_sum,");
		sql.append("       sum(busi_proc_num) as busi_proc_num,");
		sql.append("       sum(cond_num) as cond_num,");
		sql.append("       sum(atomic_num) as atomic_num,");
		sql.append("       sum(deri_num) as deri_num,");
		sql.append("       sum(dim_logic_num) as dim_logic_num,");
		sql.append("       sum(fact_logic_num) as fact_logic_num,");
		sql.append("       sum(sum_num) as sum_num");
		sql.append("  from t_asset_v_global");
		return dmJdbcTemplate.queryForList(sql.toString());
	}
	
	/**
	 * 数据接入top5
	 * 接入超过100%，只显示100%
	 * @return
	 */
	public List<Map<String,Object>> getDataAccessTop5(){
		String sql = "select project_name as busin_name,CONCAT_WS('',round((CASE WHEN access_ratio > 1 THEN 1 ELSE access_ratio END)*100,0),'%') as access_ratio from t_asset_v_access order by access_ratio desc limit 5";
		return dmJdbcTemplate.queryForList(sql);
	}
	
	/**
	 * 数据接入least5
	 * @return
	 */
	public List<Map<String,Object>> getDataAccessLeast5(){
		String sql = "select project_name as busin_name,CONCAT_WS('',round((CASE WHEN access_ratio > 1 THEN 1 ELSE access_ratio END)*100,0),'%') as access_ratio from t_asset_v_access order by access_ratio asc limit 5";
		return dmJdbcTemplate.queryForList(sql);
	}
	
	/**
	 * 数据接入总进度
	 */
	public Map<String,Object> getDataAccess(){
		String sql = "select CONCAT_WS('',round((CASE WHEN (sum(access_ratio) / count(1)) > 1 THEN 1 ELSE sum(access_ratio) / count(1) END)*100,0),'%') as total_ratio  from t_asset_v_access ";
		List<Map<String,Object>> list = dmJdbcTemplate.queryForList(sql);
		Map<String,Object> map = new HashMap<String,Object>();
		if(!list.isEmpty()) {
			map = list.get(0);
		}else {
			map.put("total_ratio","0");
		}
		return map;
	}
	
	/**
	 * 资产-流动-服务信息
	 */
	public Map<String,Object> getAssetServiceInfo() {
		Map<String, Object> paramMap = new HashMap<String, Object>();
		Map<String, Object> result = new HashMap<String, Object>();
		paramMap.put("startTime", DateFormatUtils.format(DateUtils.getCurrentTime(), "yyyy-MM-dd HH:mm:ss.SSS"));
		paramMap.put("endTime", DateFormatUtils.format(DateUtils.getYesterdayTime(), "yyyy-MM-dd HH:mm:ss.SSS"));
		StringBuilder sql = new StringBuilder();
		sql.append("select sum(blockQps)+sum(passQps) as call_count,sum(passQps)/(sum(blockQps)+sum(passQps)) as serv_rate from sentinel_metric" 
						+ " WHERE time>=$endTime and time <=$startTime");

		//List<MetricPO> metricPOS = InfluxDBUtils.queryList(sentinelDB, sql.toString(), paramMap,MetricPO.class);
		List<MetricPO> metricPOS = new ArrayList<MetricPO>();
		if(!metricPOS.isEmpty()) {
			DecimalFormat df  = new DecimalFormat("0"); 
			MetricPO mp = metricPOS.get(0);
			String serv_rate = mp.getServ_rate();
//			result.put("call_count", mp.getCall_count().substring(0,mp.getCall_count().indexOf(".")));
			result.put("serv_rate", df.format(Double.parseDouble(serv_rate)*100)+"%");
		}else {
			result.put("call_count", "0");
			result.put("serv_rate","0");
		}
		return result;
	}

	/**
	 * 资产-结构-业务板块信息
	 * @return
	 */
	public List<Map<String,Object>> getBusinessList(){
		String sql = "select busin_id,busin_code from t_meta_business_field_new order by oper_time desc";
		return dmJdbcTemplate.queryForList(sql);
	}
	
	/**
	 * 资产-结构-实体信息
	 * @param busin_id
	 * @return
	 */
	public Map<String,Object> getEntityList(String busin_id){
		String totalSql = getTotalSql();
		List<Map<String,Object>> totalList = dmJdbcTemplate.queryForList(totalSql,busin_id);
		
		Map<String,Object> totalData = new HashMap<String,Object>();
		for(Map<String,Object> totalMap : totalList){
			totalData.put(CommonUtils.valueOf(totalMap.get("biz_type")), totalMap.get("biz_count"));
		}
		if(!totalData.containsKey("biz_entity_count")){
			totalData.put("biz_entity_count", 0);
		}
		if(!totalData.containsKey("biz_other_count")){
			totalData.put("biz_other_count", 0);
		}
		if(!totalData.containsKey("biz_activity_count")){
			totalData.put("biz_activity_count", 0);
		}
		
		String searchSql = getSearchSql();
		List<Map<String,Object>> searchList = dmJdbcTemplate.queryForList(searchSql,busin_id);
		
		String bizEntitySql = getBizEntitySql();
		List<Map<String,Object>> bizEntityList = dmJdbcTemplate.queryForList(bizEntitySql,busin_id,busin_id);
		
		String bizOtherSql = "select entity_id,entity_code from t_meta_business_entity where biz_type = '业务对象' and entity_type != '1' and status = '6' and busin_id = ?";
		List<Map<String,Object>> bizOthrList = dmJdbcTemplate.queryForList(bizOtherSql,busin_id);
		
		String bizActivitySql = "select entity_id,entity_code from t_meta_business_entity where biz_type = '业务活动' and status = '6' and busin_id = ?";
		List<Map<String,Object>> bizActivityList = dmJdbcTemplate.queryForList(bizActivitySql,busin_id);
		
		Map<String,Object> data = new HashMap<String,Object>();
		data.put("total_data",totalData);
		data.put("serach_data",searchList);
		data.put("biz_entity_data",bizEntityList);
		data.put("biz_other_data",bizOthrList);
		data.put("biz_activity_data",bizActivityList);
		
		return data;
	}

	private String getBizEntitySql() {
		StringBuffer bizEntitySql = new StringBuffer();
		bizEntitySql.append("select entity.entity_id,");
		bizEntitySql.append("       entity.entity_code,");
		bizEntitySql.append("       child.entity_id    as child_entity_id,");
		bizEntitySql.append("       child.entity_code  as child_entity_code");
		bizEntitySql.append("  from t_meta_business_entity entity");
		bizEntitySql.append("  left join t_meta_business_entity child");
		bizEntitySql.append("    on entity.entity_id = child.parent_id");
		bizEntitySql.append("   and child.biz_type = '业务对象'");
		bizEntitySql.append("   and child.entity_type = '1'");
		bizEntitySql.append("   and child.status = '6'");
		bizEntitySql.append("   and child.busin_id = ?");
		bizEntitySql.append("   and child.parent_id != '0'");
		bizEntitySql.append(" where entity.biz_type = '业务对象'");
		bizEntitySql.append("   and entity.entity_type = '1'");
		bizEntitySql.append("   and entity.status = '6'");
		bizEntitySql.append("   and entity.busin_id = ?");
		bizEntitySql.append("   and entity.parent_id = '0'");
		return bizEntitySql.toString();
	}

	private String getSearchSql() {
		StringBuffer searchSql = new StringBuffer();
		searchSql.append("select entity_id,");
		searchSql.append("       entity_code,");
		searchSql.append("       case");
		searchSql.append("         when biz_type = '业务对象' then");
		searchSql.append("          case when entity_type = '1' then 'biz_entity' else 'biz_other' end");
		searchSql.append("         else 'biz_activity'");
		searchSql.append("       end as biz_type");
		searchSql.append("  from t_meta_business_entity");
		searchSql.append(" where status = '6'");
		searchSql.append("   and busin_id = ?");
		return searchSql.toString();
	}

	private String getTotalSql() {
		StringBuffer totalSql = new StringBuffer();
		totalSql.append("select biz_type, count(1) biz_count");
		totalSql.append("  from (select entity_id,");
		totalSql.append("               entity_code,");
		totalSql.append("               case");
		totalSql.append("                 when biz_type = '业务对象' then");
		totalSql.append("                  case when entity_type = '1' then 'biz_entity_count' else 'biz_other_count' end");
		totalSql.append("                 else 'biz_activity_count'");
		totalSql.append("               end as biz_type");
		totalSql.append("          from t_meta_business_entity");
		totalSql.append("         where status = '6'");
		totalSql.append("           and busin_id = ?) t");
		totalSql.append(" group by biz_type");
		return totalSql.toString();
	}
	
}